package cn.com.libertymutual.sp.dao;

import java.util.List;

import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.PagingAndSortingRepository;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import cn.com.libertymutual.sp.bean.TbSpProductConfig;

@Repository
public interface ProductConfigDao extends PagingAndSortingRepository<TbSpProductConfig, Integer>, JpaSpecificationExecutor<TbSpProductConfig> {

	List<TbSpProductConfig> findByProductId(Integer productId);

	
	List<TbSpProductConfig> findByProductIdAndBranchCode(Integer productId, String branchCode);

	@Query("select con from TbSpProductConfig con where con.productId = ?1 and find_in_set(?2,con.branchCode)<>0  and con.status=1  ")
	TbSpProductConfig findByProAndBranchCode(Integer productId, String branchCode);
	
	List<TbSpProductConfig> findByBranchCodeAndStatus(String  branchCode,String state);
	
	@Query("select pro.id,pro.riskCode,pro.productCname,con.rate,pro.productType from TbSpProductConfig con,TbSpProduct pro where con.productId = pro.id and find_in_set(?1,con.branchCode)<>0 and con.status=1 and pro.status in ('1','3') and con.isShow = 1 and con.isExclusive = 0 ")
	List<Object[]> findByBranchPro(String  branchCode);
	
	@Query("select pro.id,pro.riskCode,pro.productCname,con.rate,pro.productType from TbSpProductConfig con,TbSpProduct pro where con.productId = pro.id and find_in_set(?1,con.branchCode)<>0 and con.status=1 and pro.status in ('1','3') and con.isShow = 1 and con.isExclusive = 0 and pro.riskCode =?2")
	List<Object[]> findByBranchProRiskCode(String  branchCode,String riskCode);
	
	@Transactional
	@Modifying
	@Query("update TbSpProductConfig set status = ?1 where productId = ?2")
	void updateStatus(String status,Integer productId);

	
	
	@Transactional
	@Modifying
	@Query("update TbSpProductConfig set status = ?1 ,rate = ?2 ,isShow = '1' where branchCode=?3 and productId = ?4")
	void updateInfo(String status, Double rate, String branchCode, Integer productId);

//	@Query("select pro.rate from TbSpProductConfig pro,TbSysHotarea area   where area.areaCode=?2 and  pro.productId = ?1  and pro.status=1 and area.branchCode=pro.branchCode")
//	List<Object[]> findRateHotBybranch(String productId, String areaCode);
	
	@Query("select con from TbSpProductConfig con where con.productId = ?1 and con.branchCode =?2 and con.status=1 ")
	TbSpProductConfig findRateBybranch(Integer productId, String branchCode);

	@Transactional
	@Modifying
	@Query("update TbSpProductConfig set isExclusive = ?1 where productId = ?2")
	void setExclusive(String Exclusive, Integer productId);
	
}
